#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
BOM层级结构详细分析
"""
import openpyxl
from pathlib import Path

def analyze_bom_hierarchy(file_path, file_desc):
    """详细分析BOM的层级结构"""
    print(f"\n{'='*100}")
    print(f"{file_desc}: {Path(file_path).name}")
    print(f"{'='*100}")
    
    wb = openpyxl.load_workbook(file_path, data_only=True)
    
    # 找到BOM数据的sheet
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        
        print(f"\n{'─'*100}")
        print(f"Sheet: {sheet_name}")
        print(f"{'─'*100}")
        
        if ws.max_row < 5:
            print("  (空sheet，跳过)")
            continue
        
        # 显示前30行，详细观察结构
        print(f"\n前30行详细数据 (显示前8列):")
        print(f"{'行号':<5} {'列A':<25} {'列B':<25} {'列C':<25} {'列D':<15} {'列E':<15} {'列F':<15} {'列G':<15}")
        print("─" * 140)
        
        for row_idx in range(1, min(31, ws.max_row + 1)):
            row = list(ws.iter_rows(min_row=row_idx, max_row=row_idx, values_only=True))[0]
            
            # 获取前8列的值
            cells = []
            for i in range(min(8, len(row))):
                cell_value = row[i]
                if cell_value is None:
                    cells.append("")
                else:
                    # 转换为字符串并截断
                    cell_str = str(cell_value).replace('\n', ' ')[:24]
                    cells.append(cell_str)
            
            # 补齐到8列
            while len(cells) < 8:
                cells.append("")
            
            print(f"{row_idx:<5} {cells[0]:<25} {cells[1]:<25} {cells[2]:<25} {cells[3]:<15} {cells[4]:<15} {cells[5]:<15} {cells[6]:<15}")
        
        # 分析层级规律
        print(f"\n层级结构分析:")
        print("─" * 100)
        
        # 检查第一列是否有序号
        print("\n方式1: 检查第一列(列A)是否有序号格式 (1, 1.1, 1.1.1):")
        has_numbered_hierarchy = False
        for row_idx in range(1, min(51, ws.max_row + 1)):
            cell_a = ws.cell(row_idx, 1).value
            if cell_a and isinstance(cell_a, str):
                # 检查是否是序号格式
                if '.' in str(cell_a) or str(cell_a).isdigit():
                    parts = str(cell_a).split('.')
                    if len(parts) >= 1 and all(p.strip().isdigit() for p in parts if p.strip()):
                        level = len(parts)
                        print(f"  行{row_idx}: [{cell_a}] → 层级{level}")
                        has_numbered_hierarchy = True
                        if row_idx > 15:  # 只显示前15个示例
                            break
        
        if not has_numbered_hierarchy:
            print("  未发现序号格式的层级")
        
        # 检查是否有空单元格缩进
        print(f"\n方式2: 检查是否有空单元格缩进 (奔驰格式):")
        print(f"  分析前50行，观察列A-列D的空单元格模式...")
        
        indent_patterns = []
        for row_idx in range(1, min(51, ws.max_row + 1)):
            # 检查前4列
            cells = [ws.cell(row_idx, col).value for col in range(1, 5)]
            
            # 找到第一个非空单元格
            first_non_empty_col = None
            first_non_empty_value = None
            for col_idx, val in enumerate(cells, 1):
                if val is not None and str(val).strip():
                    first_non_empty_col = col_idx
                    first_non_empty_value = val
                    break
            
            if first_non_empty_col:
                # 计算前面有多少空列
                empty_count = first_non_empty_col - 1
                if empty_count > 0 or first_non_empty_col > 1:
                    indent_patterns.append({
                        'row': row_idx,
                        'empty_count': empty_count,
                        'first_col': first_non_empty_col,
                        'value': str(first_non_empty_value)[:30]
                    })
        
        # 显示缩进模式
        if indent_patterns:
            print(f"\n  发现{len(indent_patterns)}行有缩进模式:")
            for i, pattern in enumerate(indent_patterns[:20]):  # 只显示前20个
                indent_marker = "  " * pattern['empty_count']
                col_name = chr(64 + pattern['first_col'])  # A=1, B=2, etc.
                print(f"  行{pattern['row']:3d}: {indent_marker}[列{col_name}] {pattern['value']}")
        else:
            print("  未发现空单元格缩进模式")
        
        print()
    
    wb.close()

def main():
    """主函数"""
    excel_dir = Path("/root/odoo/extra-addons/cpq_qms/excel_data")
    
    # 分析奔驰BOM (空格缩进)
    benz_file = excel_dir / "奔驰BOM.xlsx"
    if benz_file.exists():
        analyze_bom_hierarchy(benz_file, "奔驰BOM (预期: 空单元格缩进)")
    
    print("\n\n")
    
    # 分析小米BOM (序号)
    xiaomi_file = excel_dir / "小米BOM.xlsx"
    if xiaomi_file.exists():
        analyze_bom_hierarchy(xiaomi_file, "小米BOM (预期: 序号层级)")

if __name__ == "__main__":
    main()
